<?php
	class Fund_model extends MY_Model{
		
		public function __construct(){
			parent::__construct();
		}
		
		/**
		* getFundList(): 	get the fund data, extract date from fund table for cases:			
		* 						+ get fund list
		*						+ get fund with fund id
		*						+ filter fund list by kiosk id
		*
		* @param 			int $id is id of fund, int $kid is id of kiosk
		*
		* @return    		multidimension array for list or array for 1 row
		* @access public
		*/
		public function getFundList($id=FALSE, $kid=FALSE){
			$this->db->select('f.*, k.mac_address');
			$this->db->from('fund f');
			$this->db->join('kiosk k', 'k.id = f.kiosk_id', 'left');
			
			if($id!=FALSE){
				$this->db->where('f.id = '.$id, NULL, FALSE);
				$query = $this->db->get();	
				return $query->row_array();
			}
			elseif($kid!=FALSE){
				$this->db->where('k.id = '.$kid, NULL, FALSE);
				$this->db->order_by('f.date desc');
				$this->db->limit($this->record_per_page, $this->limit);
				$query = $this->db->get();	
				return $query->result_array();
			}
			else{
				$this->db->order_by('f.date desc');
				$this->db->limit($this->record_per_page, $this->limit);
				$query = $this->db->get();	
				return $query->result_array();
			}			
		}
		
		
		/**
		* get_total(): 	get number of total records of KIOSK	
		* 
		* @param 	int $kid - the id of KIOSK
		* @return    int - number of records
		* @access public
		*/
		public function get_total($kid=FALSE){
			$this->db->select('f.*, k.mac_address');
			$this->db->from('fund f');
			$this->db->join('kiosk k', 'k.id = f.kiosk_id', 'left');
			if($kid!=FALSE){
				$this->db->where('k.id = '.$kid, NULL, FALSE);		
			}			
			$query = $this->db->get();					
			return $query->num_rows();
		}
		
		
		
		/**
		* get_total_fund_list(): 	get the KIOSK data and the donate for drawing chart in statistic	
		* 							get the data based on $type parameter
		*
		* @param 			int $type = 1 - Get the KIOSK data by KIOSK Id	
		*					int $type = 2 - Get the KIOSK data by date
		*
		* @return   		 array - records of KIOSK data		
		* @access public
		*/
		public function get_total_fund_list($type=FALSE){
			
			if($type!=FALSE && $type==1){
				$kid = $this->input->post('kid');
				$this->db->select('f.*, k.mac_address, k.name, SUM(f.donate) AS total_donate');
				$this->db->from('fund f');
				$this->db->join('kiosk k', 'k.id = f.kiosk_id', 'left');				
				$this->db->where('k.id = '.$kid, NULL, FALSE);	
				$this->db->group_by("k.id");
				$query = $this->db->get();
			}
			elseif($type!=FALSE && $type==2){
				$start_date = date('Y-m-d', strtotime($this->input->post('start_date')));
				$end_date	= date('Y-m-d', strtotime($this->input->post('end_date')));
				$kid 		= $this->input->post('kid');
				$dateRange = " f.date BETWEEN '$start_date' AND '$end_date'";
				
				$this->db->select('f.*, k.mac_address, k.name, SUM(f.donate) AS total_donate, CAST(date AS DATE) AS dateonly');
				$this->db->from('fund f');
				$this->db->join('kiosk k', 'k.id = f.kiosk_id', 'left');				
				$this->db->where('k.id = '.$kid, NULL, FALSE);
				$this->db->where($dateRange, NULL, FALSE);
				$this->db->group_by("dateonly");
				$query = $this->db->get();
				/*$this->db->where("f.date >='".$start_date."'");
				$this->db->where("f.date <='".$end_date."'");*/
			}			
			else{
				$this->db->select('f.*, k.mac_address, k.name, SUM(f.donate) AS total_donate');
				$this->db->from('fund f');
				$this->db->join('kiosk k', 'k.id = f.kiosk_id', 'left');
				$this->db->group_by("k.id");
				$query = $this->db->get();
			}
			return $query->result_array();
		}
		
		
		/**
		* save(): 	save fund data to database		
		* 
		* @param 
		* @return    
		* @access public
		*/
		public function save(){
			$id = $this->input->post('id');
			$date = date('Y-m-d h:i:s', strtotime($this->input->post('date').date('h:i:s')));
			
			$data = array(
				"kiosk_id" => $this->input->post('kiosk_id'),
				"donate" => $this->input->post('donate'),	
				"date" => $date	
			);
			//$this->db->set("date", 'NOW()', FALSE);
			if(empty($id)){
				return $this->db->insert('fund', $data);
				
			}
			else{
				$this->db->where('id', $id);
				return $this->db->update('fund', $data);
			}
		}
		
		
		/**
		* delete(): 	delete fund data		
		* 
		* @param 
		* @return    
		* @access public
		*/
		public function delete($id){
			$this->db->where('id', $id);
			$this->db->delete('fund');			
		}

		
		
		/**
		* get_top_5_kiosk(): 	Collect the top 5 KISOSK with highest donate		
		* 
		* @param 
		* @return    		array - query result 
		* @access public
		*/
		public function get_top_5_kiosk(){
			$this->db->select('k.id, k.name, SUM(f.donate) AS total_donate');
			$this->db->from('fund f');
			$this->db->join('kiosk k', 'k.id = f.kiosk_id', 'left');
			$this->db->order_by('total_donate desc');
			$this->db->group_by(array("f.kiosk_id"));
			$this->db->limit(5, 0);
			$query = $this->db->get();
			return $query->result_array();
		}
		
		
		/**
		* get_latest_kiosk_donate(): 	Collect the latest 5 KISOSK with donate		
		* 
		* @param 
		* @return    		array - query result 
		* @access public
		*/
		public function get_latest_kiosk_donate(){
			
			$this->db->select('k.id, k.name, f.donate, f.date');
			$this->db->from('fund f');
			$this->db->join('kiosk k', 'k.id = f.kiosk_id', 'left');
			$this->db->order_by('f.date desc');
			$this->db->limit(5, 0);
			$query = $this->db->get();
			//debug sql
			//echo $this->db->last_query(); 
			return $query->result_array();
		}
		

		/**
		* update_donate(): 	update the donate from ekokisosk device, using for API			
		* 
		* @param 
		* @return    
		* @access public
		*/
		public function update_donate($id=FALSE, $donate){
			if($id!=FALSE){
				// Get the current date
				$date = date('Y-m-d h:i:s');
				
				$data = array(
					"kiosk_id" => $id,
					"donate" => $donate,	
					"date" => $date					
				);			
				if($this->db->insert('fund', $data)){					
					return 1;
				}					
				else return 0;
			}
			else return 0;
		}
		
		
		/**
		* total_donate(): 	show total donate for ekiosk device, using for API			
		* 
		* @param 
		* @return    
		* @access public
		*/
		public function total_donate($id=FALSE){
			if($id!=FALSE){
				$this->db->select('k.id, k.name, SUM(f.donate) AS total_donate');
				$this->db->from('fund f');
				$this->db->join('kiosk k', 'k.id = f.kiosk_id', 'left');
				$this->db->where('k.id = '.$id, NULL, FALSE);
				$this->db->group_by(array("f.kiosk_id")); 
				$query = $this->db->get();	
				//echo $this->db->last_query();
				return $query->row_array();
			}
			else return FALSE;
		}
	}
?>